package org.charry.lib.dataset;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jxl.CellType;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.*;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.charry.lib.database_utility.DatabaseFactory;
import org.charry.lib.dataset.KAnnotation.KType;
import org.charry.lib.commons.log.StackUtility;

/**
 * This is not a light-weight container, use it carefully
 * 
 * @author wcharry
 * 
 */
public class KDataSet implements Collection {
	private static Log log = LogFactory.getLog(KDataSet.class);
	private List elements = new ArrayList();
	private List columns = new ArrayList<String>();
	private Class clazz = null;

	/**
	 * initialize KDataSet with ResultSet
	 * 
	 * @param rs
	 */
	public KDataSet(ResultSet rs) {
		try {
			dumpResultSet(rs);
		} catch (SQLException e) {
			StackUtility.logStackTrace(log, e);
		}
	}

	/**
	 * initialize KDataSet with ResultSet and target Class, one row of ResultSet
	 * will be converted to an instance of clazz
	 * 
	 * @param rs
	 * @param clazz
	 */
	public KDataSet(ResultSet rs, Class clazz) {
		this.clazz = clazz;

		try {
			dumpResultSet(rs, clazz);
		} catch (Exception e) {
			StackUtility.logStackTrace(log, e);
		}
	}

	/**
	 * initialize KDataSet with Collection and target Class, one item of
	 * Collection will be converted to an instance of clazz
	 * 
	 * @param collection
	 * @param clazz
	 */
	public KDataSet(Collection collection, Class clazz) {
		this.clazz = clazz;

		try {
			attachCollection(collection, clazz);
		} catch (Exception e) {
			StackUtility.logStackTrace(log, e);
		}
	}

	public int size() {
		return elements.size();
	}

	public Object get(int i) {
		return elements.get(i);
	}

	public Object get(int row, int col) {
		HashMap hm = (HashMap) elements.get(row);

		Iterator iterator = hm.values().iterator();
		int iCol = 0;
		while (iterator.hasNext()) {
			if (iCol == col)
				return iterator.next();

			iCol++;
		}

		return null;
	}

	public Object get(int row, String key) {
		HashMap hm = (HashMap) elements.get(row);

		return hm.get(key);
	}

	/**
	 * export KDataSet to Excel
	 * 
	 * @param filePath
	 * @param sheetName
	 * @return
	 */
	public boolean exportExcel(String filePath, String sheetName) {
		try {
			WritableWorkbook workBook = Workbook.createWorkbook(new File(
					filePath));
			WritableSheet sheet = workBook.createSheet(sheetName, 0);

			// get header info from ResultSet and export Excel
			if (this.clazz == null) {
				// render the first row
				for (int iColCnt = 0; iColCnt < columns.size(); ++iColCnt) {
					Label label = new Label(iColCnt, 0, columns.get(iColCnt)
							.toString());
					sheet.addCell(label);
				}

				// render sheet
				for (int iRowCnt = 0; iRowCnt < elements.size(); ++iRowCnt) {
					HashMap hm = (HashMap) elements.get(iRowCnt);

					Iterator iter = hm.entrySet().iterator();
					for (int iColCnt = 0; iColCnt < hm.size(); ++iColCnt) {
						String cellContent = hm.get(columns.get(iColCnt))
								.toString();
						Label label = new Label(iColCnt, iRowCnt + 1,
								cellContent);

						sheet.addCell(label);
					}
				}
			}
			// get header info from annotation & reflection and export Excel
			else {
				Field[] fields = clazz.getDeclaredFields();

				// record the valid column count, coz some columns are hidden
				int iValidColCnt = 0;

				// render the first row
				for (int iColCnt = 0; iColCnt < fields.length; iColCnt++) {
					try {
						KAnnotation kAnnotation = fields[iColCnt]
								.getAnnotation(KAnnotation.class);
						String fieldName = fields[iColCnt].getName();

						if (kAnnotation != null) {
							// if specified 'hide' attribute, then hide this
							// column
							if (kAnnotation.hide() == true)
								continue;

							// if specified a field name, then use custom name
							// instead
							if (kAnnotation.name().equals("") == false)
								fieldName = kAnnotation.name();

							if (kAnnotation.length() != -1)
								sheet.setColumnView(iColCnt,
										kAnnotation.length());
						}

						Label label = new Label(iValidColCnt++, 0, fieldName);
						sheet.addCell(label);
					} catch (IllegalArgumentException e) {
						StackUtility.logStackTrace(log, e);
					}
				}

				// render the content of Excel
				for (int iRowCnt = 0; iRowCnt < this.elements.size(); ++iRowCnt) {
					iValidColCnt = 0;
					for (int iColCnt = 0; iColCnt < fields.length; iColCnt++) {
						try {
							KAnnotation kAnnotation = fields[iColCnt]
									.getAnnotation(KAnnotation.class);

							CellType cellType = CellType.STRING_FORMULA;
							if (kAnnotation != null) {
								if (kAnnotation.hide() == true)
									continue;

								if (kAnnotation.type() == KType.INT
										|| kAnnotation.type() == KType.DOUBLE
										|| kAnnotation.type() == KType.FLOAT)
									cellType = CellType.NUMBER;
							}

							fields[iColCnt].setAccessible(true);
							Object value = fields[iColCnt].get(this.elements
									.get(iRowCnt));

							if (cellType == CellType.NUMBER) {
								int iValue = (Integer) value;
								if (value == null)
									iValue = 0;

								Number number = new Number(iValidColCnt++,
										iRowCnt + 1, iValue);
								sheet.addCell(number);
							} else {
								String strValue = "";
								if (value == null)
									strValue = "N/A";
								else
									strValue = "" + value;
								Label label = new Label(iValidColCnt++,
										iRowCnt + 1, strValue);
								sheet.addCell(label);
							}
						} catch (IllegalArgumentException e) {
							StackUtility.logStackTrace(log, e);
						} catch (IllegalAccessException e) {
							StackUtility.logStackTrace(log, e);
						}
					}
				}
			}

			workBook.write();
			workBook.close();
		} catch (IOException e) {
			StackUtility.logStackTrace(log, e);
		} catch (WriteException e) {
			StackUtility.logStackTrace(log, e);
		} catch (Exception e) {
			StackUtility.logStackTrace(log, e);
		}

		return true;
	}

	public boolean exportCSV(String filePath) {
		return true;
	}

	public boolean exportKDF(String filePath) {
		return true;
	}

	/**
	 * export KDataSet to a html page
	 * 
	 * @param filePath
	 * @return
	 */
	public boolean exportHTML(String filePath) {
		try {
			PrintStream printStream = new PrintStream(new FileOutputStream(
					filePath));
			printStream.println(getHTML());
		} catch (FileNotFoundException e) {
			StackUtility.logStackTrace(log, e);
		}

		return true;
	}

	/**
	 * return a html table
	 * 
	 * @return
	 */
	public String getHTML(String tableId, String tableClass) {
		StringBuilder sb = new StringBuilder();
		sb.append("<table id='" + tableId + "' class='" + tableClass
				+ "' border='1'>");

		// get header info from ResultSet
		if (this.clazz == null) {
			// render table header
			sb.append("<tr>");
			for (int iColCnt = 0; iColCnt < columns.size(); ++iColCnt) {
				sb.append("<th>");
				sb.append(columns.get(iColCnt));
				sb.append("</th>");
			}
			sb.append("</tr>");

			// render table body
			for (int iRowCnt = 0; iRowCnt < elements.size(); ++iRowCnt) {
				HashMap hm = (HashMap) elements.get(iRowCnt);

				sb.append("<tr>");
				Iterator iter = hm.entrySet().iterator();
				for (int iColCnt = 0; iColCnt < hm.size(); ++iColCnt) {
					sb.append("<td id='p" + tableId + iRowCnt + iColCnt + "'>");
					sb.append(hm.get(columns.get(iColCnt)));
					sb.append("</td>");
				}

				sb.append("</tr>");
			}
		} else {
			// render table header
			sb.append("<tr>");
			Field[] fields = clazz.getDeclaredFields();
			for (int iColCnt = 0; iColCnt < fields.length; iColCnt++) {
				try {
					KAnnotation kAnnotation = fields[iColCnt]
							.getAnnotation(KAnnotation.class);
					String fieldName = fields[iColCnt].getName();
					if (kAnnotation != null) {
						if (kAnnotation.hide() == true)
							continue;

						if (kAnnotation.name().equals("") == false)
							fieldName = kAnnotation.name();
					}

					sb.append("<th>");
					sb.append(fieldName);
					sb.append("</th>");
				} catch (IllegalArgumentException e) {
					StackUtility.logStackTrace(log, e);
				}
			}
			sb.append("</tr>");

			// render table body
			for (int iRowCnt = 0; iRowCnt < this.elements.size(); ++iRowCnt) {
				// log.info(this.elements.get(i));
				sb.append("<tr>");
				for (int iColCnt = 0; iColCnt < fields.length; iColCnt++) {
					try {
						KAnnotation kAnnotation = fields[iColCnt]
								.getAnnotation(KAnnotation.class);
						if (kAnnotation != null) {
							if (kAnnotation.hide() == true)
								continue;
						}

						fields[iColCnt].setAccessible(true);
						Object value = fields[iColCnt].get(this.elements
								.get(iRowCnt));
						log.info(value);
						sb.append("<td id='p" + tableId + iRowCnt + iColCnt
								+ "'>");
						if (value == null)
							sb.append("N/A");
						else
							sb.append(value);
						sb.append("</td>");
					} catch (IllegalArgumentException e) {
						StackUtility.logStackTrace(log, e);
					} catch (IllegalAccessException e) {
						StackUtility.logStackTrace(log, e);
					}
				}

				sb.append("</tr>");
			}
		}

		sb.append("</table>");
		log.info(sb.toString());

		return sb.toString();
	}

	public String getHTML() {
		return getHTML("k-dataset", "k-class");
	}

	private void dumpResultSet(ResultSet rs) throws SQLException {
		ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
		int columnCount = md.getColumnCount();

		// get column info
		fetchColumnInfo(rs);

		while (rs.next()) {
			Map rowData = new HashMap();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}

			elements.add(rowData);
		}
	}

	private void dumpResultSet(ResultSet rs, Class clazz) throws Exception {
		ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();
		int colMaxCnt = metaData.getColumnCount();
		Field[] fields = clazz.getDeclaredFields();

		while (rs.next()) {
			Object newInstance = clazz.newInstance();

			for (int iColCnt = 1; iColCnt <= colMaxCnt; iColCnt++) {
				try {
					Object value = rs.getObject(iColCnt);
					for (int iFieldCnt = 0; iFieldCnt < fields.length; iFieldCnt++) {
						Field f = fields[iFieldCnt];
						if (f.getName().equalsIgnoreCase(
								metaData.getColumnName(iColCnt).replaceAll("_",
										""))) {

							BeanUtils.copyProperty(newInstance, f.getName(),
									value);
						}
					}
				} catch (Exception e) {
					StackUtility.logStackTrace(log, e);
				}
			}

			elements.add(newInstance);
		}
	}

	private void attachCollection(Collection collection, Class clazz)
			throws Exception {

		Iterator iterator = collection.iterator();

		while (iterator.hasNext()) {
			Object element = iterator.next();
			Object newInstance = clazz.newInstance();
			BeanUtils.copyProperties(newInstance, element);

			elements.add(newInstance);
		}
	}

	private void fetchColumnInfo(ResultSet rs) {
		try {
			ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();

			for (int i = 1; i <= md.getColumnCount(); i++) {
				columns.add(md.getColumnName(i));
			}
		} catch (SQLException e) {
			StackUtility.logStackTrace(log, e);
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// test1();
		test2();
	}

	private static void test1() {
		String sql = "SELECT * FROM users";
		ResultSet rs = DatabaseFactory.getInstance("mysql").executeQuery(sql);

		KDataSet ds = new KDataSet(rs);

		// for (int i = 0; i < ds2.size(); ++i) {
		// UserInfo o = (UserInfo) ds2.get(i);
		// log.info("User:" + o.getUser() + " Password:" + o.getPassword()
		// + " Time:" + o.getT());
		// }

		DatabaseFactory.getInstance("mysql").closeConnection();

		ds.exportExcel("d:/x.xls", "sheet1");
		ds.exportHTML("d:/x.html");
	}

	private static void test2() {
		List list = new ArrayList<UserInfo>();
		UserInfo u1 = new UserInfo();
		u1.setId(1);
		u1.setUser("Charry");
		u1.setPassword("passwd1");

		UserInfo u2 = new UserInfo();
		u2.setId(2);
		u2.setUser("Bombers");
		u2.setPassword("passwd2");
		u2.setJoinTime("2010-03-22 21:22:42");

		UserInfo u3 = new UserInfo();
		u3.setId(4);
		u3.setUser("Lady Gaga");
		u3.setPassword("passwd3");
		u3.setJoinTime("2012-03-22 21:22:32");

		list.add(u1);
		list.add(u2);
		list.add(u3);

		KDataSet ds = new KDataSet(list, UserInfo.class);

		ds.exportHTML("d:/x.html");
		ds.exportExcel("d:/x.xls", "sheet1");
	}

	public boolean add(Object arg0) {
		return this.elements.add(arg0);
	}

	public boolean addAll(Collection arg0) {
		return this.elements.addAll(arg0);
	}

	public void clear() {
		this.elements.clear();
		this.columns.clear();
	}

	public boolean contains(Object arg0) {
		return false;
	}

	public boolean containsAll(Collection arg0) {
		return false;
	}

	public boolean isEmpty() {
		return this.elements.isEmpty();
	}

	public Iterator iterator() {
		return null;
	}

	public boolean remove(Object arg0) {
		return this.elements.remove(arg0);
	}

	public boolean removeAll(Collection arg0) {
		return this.columns.removeAll(arg0);
	}

	public boolean retainAll(Collection arg0) {
		return false;
	}

	public Object[] toArray() {
		return null;
	}

	public Object[] toArray(Object[] arg0) {
		return null;
	}
}
